When building direct models to directly query a datasource, you have the option to add a custom table based on a SQL script. This feature is also available in Model Pro.
How to Add a Custom Query Table
To add a custom table based on a SQL script, click the 'Custom Query' button from the table selection list:
From the Custom Query dialog, enter a name for the table in the 'Table Name' field, and write your SQL script in the query editor. Test your script before clicking Add.
Example
In this example, the Substring function is used to create a new table by extracting substrings from the firstName column in the Customers table.
SELECT SUBSTRING(FirstName, 1, 3) AS ExtractString FROM Customers
Name the new table and write your SQL script. Test the script before clicking the Add button:
The new table is added to the table schema; to rename the column(s) in the custom table, right click on the relevant column and select Rename from the context menu.
In this example, for the FirstName and LastName columns are hidden from the customers table (blue highlight). As usual, click the Finish button (green arrow) to process the model.
When the new model is opened, we see the custom table in the Dimensions panel (red highlight below), and can add its column(s) to the query (green arrow).
